package gov.nhso.prov.acc.apps.acc6;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.net.SocketException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPReply;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;

import com.zehon.exception.FileTransferException;
import com.zehon.ftp.FTP;

import gov.nhso.prov.acc.beans.Acc6ChequeBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseChequeBean;
import gov.nhso.prov.acc.beans.Acc6TempChequeStmtBean;
import gov.nhso.prov.acc.beans.Acc6TransactionBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.ftp.FTPUtil;
import gov.nhso.prov.acc.utils.DateUtil;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings({"rawtypes"})
public class ACC60308 extends Utility{
	public String url = "/apps/acc6/ACC60308A.jsp";

	public ACC60308(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if (job.equals("init")) {
			this.init(request);
		} else if (job.equals("updateStatement")) {
			
			 this.updateStatement(request);
			
			// Test Unit
			// this.testClientDataTranfer(request) ;
			
		} else if (job.equals("search")){
			this.setLastRequest(request, "lastSearch");
			this.search(request);
		} else if (job.equals("view")){
			this.view(request);
		} else if (job.equals("back")) {
			if (this.getLastRequest(request, "lastSearch")) {
				this.search(request);
			} else {
				this.init(request);
			}
		} else if (job.equals("save")) {
			this.save(request);
			if (this.getLastRequest(request, "lastSearch")) {
				this.search(request);
			} else {
				this.init(request);
			}
		} else if (job.equals("del")) {
			this.del(request);
		}
	}
	
	private String init(HttpServletRequest request){
		request.setAttribute("job", "initTable");
		return this.url;
	}
	
	/**
	 * Unit test
	 * @param request
	 * @return
	 */
	private String testClientDataTranfer(HttpServletRequest  request){
		

		
		System.out.println("---- Process name updateStatement -----");
		Connection conn = null;
		ISUD dao = null;
		
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			StringBuffer sb = new StringBuffer();
			FTPUtil fu = new FTPUtil();
			Map mapResult = new HashMap();
			int countResult = 0;
			InputStream in = null;
			String nameOfFile = "";
			String[] splitStr = null;
			String provinceId = "";
			String chequeNo = "";
			String chequeAmount = "";
			String chequeReceivingDate = "";
			String chequeType = "";
			String chqStmtKey = "";
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			startDate = this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
			endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
		
			FTPClient client = new FTPClient();
			
			try { 
			
				int  reply = 0;
				
				client.connect(fu.ftpHost);
				client.login(fu.ftpUserId, fu.ftpPasswd);
				reply  = client.getReplyCode();
				
				if(!FTPReply.isPositiveCompletion(reply)) {
				  	client.disconnect();
				    System.err.println("FTP server refused connection.");
				    System.exit(1);
				 }
			
				String remoteFolder = fu.ftpBaac2NhsoCheque+"";
				
				FTPFile[] f = client.listFiles(remoteFolder);
				List<String> lsFolderName = new ArrayList<String>();
				String[] arrStr = null;
				int fileName = 0;
				int iStartDate = Integer.parseInt(startDate);
				int iEndDate = Integer.parseInt(endDate);
				
				for (int n = 0; n < f.length; n++) {
					FTPFile ff = f[n];
					if (!ff.getName().equals(".") && !ff.getName().equals("..")) {
						fileName = Integer.parseInt(ff.getName());
						if (fileName >= iStartDate && fileName <= iEndDate) {
							lsFolderName.add(ff.getName());
						}
					}
				}
				
				for (int i = 0 ; i < lsFolderName.size() ; i++) {
					
					remoteFolder = fu.ftpBaac2NhsoCheque+"/"+lsFolderName.get(i)+"/";
					
					f = client.listFiles(remoteFolder);
					
					for (int n = 0; n < f.length; n++) {
						
						FTPFile ff = f[n];
						
						if (!ff.getName().equals(".") && !ff.getName().equals("..")) {
							
							nameOfFile = ff.getName();
							arrStr = nameOfFile.split("-");
							provinceId = this.dataUtil(dao).getProvinceIdByAccountDaily(arrStr[1]);
							
							if (provinceId != null && !provinceId.equals("")) {
										
								// in = FTP.getFileAsStream(nameOfFile, remoteFolder, fu.ftpHost,  fu.ftpUserId,  fu.ftpPasswd);
								System.out.println("Before FTP file : " + n);
								
							 in =  getFileFTPProvacc(fu.ftpHost,  fu.ftpUserId,  fu.ftpPasswd , nameOfFile, remoteFolder);
								
							// 	InputStream in = client.retrieveFileStream(remote)
								
								
								System.out.println(" status file >  " + in.available());
								
								InputStreamReader is = new InputStreamReader(in, "TIS-620");
								BufferedReader br = new BufferedReader(is);
								String read = br.readLine();
								
								try { 
											
										while (read != null) {
											
											splitStr = read.split("\\|");
											chequeType = splitStr[14].trim();
											chequeNo = splitStr[12].trim();
											chequeAmount = splitStr[13].trim();
											chequeReceivingDate = this.dateUtil().changeDateToDB(fu.changeFormatDate(splitStr[3].trim()));
											
											//Old 
											//chqStmtKey = dao.chkData(splitStr[3]).trim() + dao.chkData(splitStr[4]).trim();
											
											//New
											chqStmtKey = provinceId + dao.chkData(splitStr[3]).trim() + dao.chkData(splitStr[4]).trim();
											
											if (chequeType.equals("D")) {
											
												sb.setLength(0);
												sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO FROM "+this.schema+".ACC6_TRANSACTION ");
												sb.append("WHERE PROVINCE_ID = '" + provinceId + "' AND CHEQUE_NO = '" + chequeNo +"' AND EXPENSE_AMOUNT = "+chequeAmount+" ");
												sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+" AND REASON_CODE = '00' ");
												
												//System.out.println("sql line 142 >> " + sb.toString());
												
												mapResult = dao.findDataSql(sb.toString());
												countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
												
												//System.out.println("amount COUNT_CHEQUE_NO : " + countResult);
												
												if (countResult <= 0) {
													
													sb.setLength(0);
													sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO FROM "+this.schema+".ACC6_TRANSACTION ");
													sb.append("WHERE CHQ_STMT_KEY = '"+chqStmtKey+"' AND PROVINCE_ID = '"+provinceId+"' ");
													
													// System.out.println("sql line 146 >> " + sb.toString());
													
													mapResult = dao.findDataSql(sb.toString());
													countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
													
													if (countResult <= 0) {
													
														sb.setLength(0);
														sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO,TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP FROM "+this.schema+".ACC6_TRANSACTION ");
														sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND EXPENSE_AMOUNT = "+chequeAmount+" ");
														sb.append("AND REASON_CODE IS NULL GROUP BY TRANSACTION_TIMESTAMP");
													
														//System.out.println("sql line 154 >> " + sb.toString());
														
														mapResult = dao.findDataSql(sb.toString());
														
														if (!dao.chkData(mapResult.get("COUNT_CHEQUE_NO")).equals("") && !dao.chkData(mapResult.get("TRANSACTION_TIMESTAMP")).equals("")) {
														
															countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
															String transactionTimestamp = dao.chkData(mapResult.get("TRANSACTION_TIMESTAMP"));
															
															if (countResult == 1) {
																
																sb.setLength(0);
																sb.append("UPDATE "+this.schema+".ACC6_TRANSACTION ");
																sb.append("SET CHEQUE_RECEIVING_DATE = "+chequeReceivingDate+",CHQ_STMT_KEY = '"+chqStmtKey+"', ");
																sb.append("REASON_CODE = '00' ");
																sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND EXPENSE_AMOUNT = "+chequeAmount+" ");
																System.out.println("sql line 165 >> " + sb.toString());
																dao.execDataSql(sb.toString());
																
																sb.setLength(0);
																sb.append("UPDATE "+this.schema+".ACC6_EXPENSE_CHEQUE ");
																sb.append("SET CHEQUE_RECEIVING_DATE = "+chequeReceivingDate+" ");
																sb.append("WHERE TRANSACTION_TIMESTAMP = TO_TIMESTAMP('"+transactionTimestamp+"','YYYYMMDDHH24MISSFF3') ");
																System.out.println("sql line 171 >> " + sb.toString());
																dao.execDataSql(sb.toString());
																
																sb.setLength(0);
																sb.append("DELETE FROM "+this.schema+".ACC6_TEMP_CHEQUE_STMT ");
																sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND CHEQUE_AMOUNT = "+chequeAmount+" ");
																sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+" AND CHQ_STMT_KEY = '"+chqStmtKey+"' ");
																System.out.println("sql line 177 >> " + sb.toString());
																dao.execDataSql(sb.toString());
																
															}
															
														} else { //เช็คหลายกองทุน
															
															sb.setLength(0);
															sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO FROM "+this.schema+".ACC6_CHEQUE ");
															sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND SUM_CHEQUE_AMOUNT = "+chequeAmount+" ");
															sb.append("AND CHEQUE_STATUS = 'W' AND ACTIVE = 'Y' ");
															System.out.println("sql line 185 >> " + sb.toString());
															mapResult = dao.findDataSql(sb.toString());
															countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
															
															if (countResult == 1) {
															
																sb.setLength(0);
																sb.append("UPDATE "+this.schema+".ACC6_TRANSACTION ");
																sb.append("SET CHEQUE_RECEIVING_DATE = "+chequeReceivingDate+",CHQ_STMT_KEY = '"+chqStmtKey+"', ");
																sb.append("REASON_CODE = '00' ");
																sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' ");
																System.out.println("sql line 194 >> " + sb.toString());
																dao.execDataSql(sb.toString());
																
																sb.setLength(0);
																sb.append("UPDATE "+this.schema+".ACC6_CHEQUE ");
																sb.append("SET CHEQUE_RECEIVING_DATE = "+chequeReceivingDate+" ");
																sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND SUM_CHEQUE_AMOUNT = "+chequeAmount+" ");
																System.out.println("sql line 200 >> " + sb.toString());
																dao.execDataSql(sb.toString());
																
																sb.setLength(0);
																sb.append("DELETE FROM "+this.schema+".ACC6_TEMP_CHEQUE_STMT ");
																sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND CHEQUE_AMOUNT = "+chequeAmount+" ");
																sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+" AND CHQ_STMT_KEY = '"+chqStmtKey+"' ");
																System.out.println("sql line 206" + sb.toString());
																dao.execDataSql(sb.toString());
															
															}
														}
														
														countResult = 0;
														
														sb.setLength(0);
														sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO FROM "+this.schema+".ACC6_TRANSACTION ");
														sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND EXPENSE_AMOUNT = "+chequeAmount+" ");
														sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+"");
														mapResult = dao.findDataSql(sb.toString());
														countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
														
														if (countResult <= 0) {
														
															sb.setLength(0);
															sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO FROM "+this.schema+".ACC6_TRANSACTION ");
															sb.append("WHERE CHQ_STMT_KEY = '"+chqStmtKey+"' ");
															mapResult = dao.findDataSql(sb.toString());
															countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
															
															if (countResult <= 0) {
																
																String txtCondition = "";
															
																if (dao.chkData(chequeNo).equals("")) {
																	txtCondition = " AND CHEQUE_NO IS NULL ";
																} else {
																	txtCondition = " AND CHEQUE_NO = '"+chequeNo+"' ";
																}
																
																countResult = 0;
																sb.setLength(0);
																sb.append("SELECT COUNT(PROVINCE_ID) AS COUNT_CHEQUE_NO FROM "+this.schema+".ACC6_TEMP_CHEQUE_STMT ");
																sb.append("WHERE PROVINCE_ID = '"+provinceId+"' "+txtCondition+" AND CHEQUE_AMOUNT = "+chequeAmount+" ");
																sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+" AND CHQ_STMT_KEY = '"+chqStmtKey+"' ");
																mapResult = dao.findDataSql(sb.toString());
																countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
																
																if (countResult <= 0) {
																	sb.setLength(0);
																	sb.append("INSERT INTO " + this.schema + ".ACC6_TEMP_CHEQUE_STMT (PROVINCE_ID,CHEQUE_NO,CHEQUE_AMOUNT,CHEQUE_RECEIVING_DATE,CREATED_DATE,CHQ_STMT_KEY) ");
																	sb.append("VALUES ('"+provinceId+"','"+chequeNo+"',"+chequeAmount+","+chequeReceivingDate+",(SYSDATE),'"+chqStmtKey+"') ");
																	dao.execDataSql(sb.toString());
																}
																
															}
														}
													}
												}
											}
											
											read = br.readLine();
											
										} // End While read file
										
										} catch (FileNotFoundException ex) { 
											System.out.println("Error FileNotFoundException : " + ex.toString());
							
										} catch (Exception ex) { 
											System.out.println("Error : " +ex.toString());
											ex.printStackTrace();
											
										} finally {
											
											 try{ 
												 
												// Return Buffer 
												br.close();
												
												// Return File
												is.close();
												
												// Return input stream file
												in.close(); 
												
											 } catch(Exception ex){ 
												 ex.printStackTrace();
											 }
										}
								conn.commit();
							}
						}
					} // End for read file data.
					
					String sql = "SELECT DOC_NO FROM " + this.schema + ".ACC6_EXPENSE WHERE EXPENSE_STATUS IN ('W','S') AND EXPENSE_TYPE = 'C'";
					List lsDocNo = dao.listDataSql(sql);
					
					for (int n = 0; n < lsDocNo.size(); n++) {
						
						Map map = (Map) lsDocNo.get(n);
						String docNo = dao.chkData(map.get("DOC_NO"));
						String num1 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE WHERE DOC_NO = '"  + docNo + "'").get("NUM"));
						String num2 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND B.REASON_CODE = '00' ").get("NUM"));
						String num3 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND (B.REASON_CODE <> '00' AND B.REASON_CODE IS NOT NULL) ").get("NUM"));
						
						if (!num2.equals("0")) {
							
							Acc6ExpenseBean aeb = new Acc6ExpenseBean();
							aeb.setDocNo(docNo);
							
							if (num1.equals(num2)) {
								aeb.setExpenseStatus("Y");
							} else {
								aeb.setExpenseStatus("S");
							}
							
							aeb.setUpdatedBy("BAAC");
							aeb.setUpdatedDate("(SYSDATE)");
							dao.update(aeb);
						
						} else if (!num3.equals("0") && num1.equals(num3)) {
							Acc6ExpenseBean aeb = new Acc6ExpenseBean();
							aeb.setDocNo(docNo);
							aeb.setExpenseStatus("N");
							aeb.setUpdatedBy("BAAC");
							aeb.setUpdatedDate("(SYSDATE)");
							dao.update(aeb);
						}
					}
					
					//cheque หลายกองทุน
					sql = "SELECT DOC_NO,SUM_CHEQUE_AMOUNT,PROVINCE_ID,CHEQUE_NO FROM " + this.schema + ".ACC6_CHEQUE WHERE CHEQUE_STATUS = 'W' ";
					sb = new StringBuffer();
					lsDocNo = dao.listDataSql(sql);
					
					for (int n = 0; n < lsDocNo.size(); n++) {
					
						Map map = (Map) lsDocNo.get(n);
						chequeNo = dao.chkData(map.get("CHEQUE_NO"));
						String docNo = dao.chkData(map.get("DOC_NO"));
						provinceId = dao.chkData(map.get("PROVINCE_ID"));
						String sumChequeAmount1 = dao.chkData(map.get("SUM_CHEQUE_AMOUNT"));
						String sumChequeAmount2 = dao.chkData(dao.findDataSql("SELECT SUM(CHEQUE_AMOUNT) AS SUM_CHEQUE_AMOUNT FROM " + this.schema + ".ACC6_CHEQUE_DETAIL A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND B.REASON_CODE = '00' ").get("SUM_CHEQUE_AMOUNT"));
						
						if (sumChequeAmount1.equals(sumChequeAmount2)) {
							//get CHEQUE_RECEIVING_DATE from table Acc6Transaction
							sb.setLength(0);
							sb.append("SELECT DISTINCT CHEQUE_RECEIVING_DATE ");
							sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
							sb.append("WHERE CHEQUE_NO = '" + chequeNo + "' AND PROVINCE_ID = '" + provinceId + "' ");
							sb.append("AND REASON_CODE = '00' ");
							Map mapChequeReceivingDate = dao.findDataSql(sb.toString()); //CHEQUE_RECEIVING_DATE
							if (!dao.chkData(mapChequeReceivingDate.get("CHEQUE_RECEIVING_DATE")).equals("")) {
								Acc6ChequeBean acb = new Acc6ChequeBean();
								acb.setDocNo(docNo);
								acb.setChequeStatus("Y");
								acb.setUpdatedDate("(SYSDATE)");
								acb.setUpdatedBy("BAAC");
								acb.setChequeReceivingDate("TO_DATE('"+this.dateUtil().changeDateFromDB(mapChequeReceivingDate.get("CHEQUE_RECEIVING_DATE").toString(), "")+"','DD.MM.YYYY')");
								dao.update(acb);
								dao.printMessage();
							}
						}
					}
				} // end list folder data  of file
			
			// Old
		    //client.disconnect();
		    
		    this.message(request).setMsg("I", "บันทึกข้อมูลเสร็จสิ้น");
		    
		    /*System.out.println(" ======= Start printMessage ACC60308 ======= ");
			dao.printMessage();
		    System.out.println(" ======= End printMessage ACC60308 ======= ");*/
			conn.commit();
				
			
			} catch (SocketException  ex){
				
				ex.printStackTrace();
				
			} catch(Exception ex) { 
				
				System.out.println("Error : " +ex.toString()); 	
				ex.printStackTrace();
				dao.printMessage();
				
				// this.message(request).setMsg("E", ex.toString() + ":" + dao.getMessage(), ex.getStackTrace());
				 	
			} finally { 
				
					if(client.isConnected()){ 
							
						try { 
									// Close Cache
									//FTP.closeCache();
							
									System.out.println(" FTP Client status :  "  + client.getStatus() );
									System.out.println(" FTP isConnection  1 : "  + client.isConnected());
									 
									// Logout 
									client.logout();
									System.out.println("Client logout.");
									
									//disconnection
									client.disconnect();
									 System.out.println("Client Disconnction.");
									 
									 System.out.println(" FTP isConnection  2 : "  + client.isConnected());
									 
							} catch (Exception ex ){ 
								System.out.println("Error : " + ex.toString());
								ex.printStackTrace();
								this.message(request).setMsg("E", ex.toString() + ":" + dao.getMessage(), ex.getStackTrace());
							}
					} // end if check connection  
					
			} // end finaly 
			
		} catch (Exception e) {
			
			try {
			
				if (conn != null) {
					conn.rollback();
				}
		
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
			dao.printMessage();
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	
		
	}
	
	
	// Old
	private synchronized String updateStatement(HttpServletRequest request){
		
		System.out.println("---- Process name updateStatement -----");
		Connection conn = null;
		ISUD dao = null;
		
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			StringBuffer sb = new StringBuffer();
			FTPUtil fu = new FTPUtil();
			Map mapResult = new HashMap();
			int countResult = 0;
			
			String nameOfFile = "";
			String[] splitStr = null;
			String provinceId = "";
			String chequeNo = "";
			String chequeAmount = "";
			String chequeReceivingDate = "";
			String chequeType = "";
			String chqStmtKey = "";
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			startDate = this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
			endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
		
			FTPClient client = new FTPClient();
			
			try { 
			
				int  reply = 0;
				
				client.connect(fu.ftpHost);
				client.login(fu.ftpUserId, fu.ftpPasswd);
				reply  = client.getReplyCode();
				
				if(!FTPReply.isPositiveCompletion(reply)) {
				  	client.disconnect();
				    System.err.println("FTP server refused connection.");
				    System.exit(1);
				 }
			
				String remoteFolder = fu.ftpBaac2NhsoCheque+"";
				
				FTPFile[] f = client.listFiles(remoteFolder);
				List<String> lsFolderName = new ArrayList<String>();
				String[] arrStr = null;
				int fileName = 0;
				int iStartDate = Integer.parseInt(startDate);
				int iEndDate = Integer.parseInt(endDate);
				
				for (int n = 0; n < f.length; n++) {
					FTPFile ff = f[n];
					if (!ff.getName().equals(".") && !ff.getName().equals("..")) {
						fileName = Integer.parseInt(ff.getName());
						if (fileName >= iStartDate && fileName <= iEndDate) {
							lsFolderName.add(ff.getName());
						}
					}
				}
				
				for (int i = 0 ; i < lsFolderName.size() ; i++) {
					
					remoteFolder = fu.ftpBaac2NhsoCheque+"/"+lsFolderName.get(i)+"/";
					
					f = client.listFiles(remoteFolder);
					
					for (int n = 0; n < f.length; n++) {
						
						FTPFile ff = f[n];
						
						if (!ff.getName().equals(".") && !ff.getName().equals("..")) {
							
							nameOfFile = ff.getName();
							arrStr = nameOfFile.split("-");
							provinceId = this.dataUtil(dao).getProvinceIdByAccountDaily(arrStr[1]);
							
							if (provinceId != null && !provinceId.equals("")) {
										
								// in = FTP.getFileAsStream(nameOfFile, remoteFolder, fu.ftpHost,  fu.ftpUserId,  fu.ftpPasswd);
								System.out.println("Before FTP file : " + n);
								
							 InputStream in =  getFileFTPProvacc(fu.ftpHost,  fu.ftpUserId,  fu.ftpPasswd , nameOfFile, remoteFolder);
								
							// 	InputStream in = client.retrieveFileStream(remote)
								
								
								System.out.println(" status file >  " + in.available());
								
								InputStreamReader is = new InputStreamReader(in, "TIS-620");
								BufferedReader br = new BufferedReader(is);
								String read = br.readLine();
								
								try { 
											
										while (read != null) {
											
											splitStr = read.split("\\|");
											chequeType = splitStr[14].trim();
											chequeNo = splitStr[12].trim();
											chequeAmount = splitStr[13].trim();
											chequeReceivingDate = this.dateUtil().changeDateToDB(fu.changeFormatDate(splitStr[3].trim()));
											
											//Old 
											//chqStmtKey = dao.chkData(splitStr[3]).trim() + dao.chkData(splitStr[4]).trim();
											
											//New
											chqStmtKey = provinceId + dao.chkData(splitStr[3]).trim() + dao.chkData(splitStr[4]).trim();
											
											if (chequeType.equals("D")) {
											
												sb.setLength(0);
												sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO FROM "+this.schema+".ACC6_TRANSACTION ");
												sb.append("WHERE PROVINCE_ID = '" + provinceId + "' AND CHEQUE_NO = '" + chequeNo +"' AND EXPENSE_AMOUNT = "+chequeAmount+" ");
												sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+" AND REASON_CODE = '00' ");
												
												//System.out.println("sql line 142 >> " + sb.toString());
												
												mapResult = dao.findDataSql(sb.toString());
												countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
												
												//System.out.println("amount COUNT_CHEQUE_NO : " + countResult);
												
												if (countResult <= 0) {
													
													sb.setLength(0);
													sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO FROM "+this.schema+".ACC6_TRANSACTION ");
													sb.append("WHERE CHQ_STMT_KEY = '"+chqStmtKey+"' AND PROVINCE_ID = '"+provinceId+"' ");
													
													// System.out.println("sql line 146 >> " + sb.toString());
													
													mapResult = dao.findDataSql(sb.toString());
													countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
													
													if (countResult <= 0) {
													
														sb.setLength(0);
														sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO,TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS TRANSACTION_TIMESTAMP FROM "+this.schema+".ACC6_TRANSACTION ");
														sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND EXPENSE_AMOUNT = "+chequeAmount+" ");
														sb.append("AND REASON_CODE IS NULL GROUP BY TRANSACTION_TIMESTAMP");
													
														//System.out.println("sql line 154 >> " + sb.toString());
														
														mapResult = dao.findDataSql(sb.toString());
														
														if (!dao.chkData(mapResult.get("COUNT_CHEQUE_NO")).equals("") && !dao.chkData(mapResult.get("TRANSACTION_TIMESTAMP")).equals("")) {
														
															countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
															String transactionTimestamp = dao.chkData(mapResult.get("TRANSACTION_TIMESTAMP"));
															
															if (countResult == 1) {
																
																sb.setLength(0);
																sb.append("UPDATE "+this.schema+".ACC6_TRANSACTION ");
																sb.append("SET CHEQUE_RECEIVING_DATE = "+chequeReceivingDate+",CHQ_STMT_KEY = '"+chqStmtKey+"', ");
																sb.append("REASON_CODE = '00' ");
																sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND EXPENSE_AMOUNT = "+chequeAmount+" ");
																System.out.println("sql line 165 >> " + sb.toString());
																dao.execDataSql(sb.toString());
																
																sb.setLength(0);
																sb.append("UPDATE "+this.schema+".ACC6_EXPENSE_CHEQUE ");
																sb.append("SET CHEQUE_RECEIVING_DATE = "+chequeReceivingDate+" ");
																sb.append("WHERE TRANSACTION_TIMESTAMP = TO_TIMESTAMP('"+transactionTimestamp+"','YYYYMMDDHH24MISSFF3') ");
																System.out.println("sql line 171 >> " + sb.toString());
																dao.execDataSql(sb.toString());
																
																sb.setLength(0);
																sb.append("DELETE FROM "+this.schema+".ACC6_TEMP_CHEQUE_STMT ");
																sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND CHEQUE_AMOUNT = "+chequeAmount+" ");
																sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+" AND CHQ_STMT_KEY = '"+chqStmtKey+"' ");
																System.out.println("sql line 177 >> " + sb.toString());
																dao.execDataSql(sb.toString());
																
															}
															
														} else { //เช็คหลายกองทุน
															
															sb.setLength(0);
															sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO FROM "+this.schema+".ACC6_CHEQUE ");
															sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND SUM_CHEQUE_AMOUNT = "+chequeAmount+" ");
															sb.append("AND CHEQUE_STATUS = 'W' AND ACTIVE = 'Y' ");
															System.out.println("sql line 185 >> " + sb.toString());
															mapResult = dao.findDataSql(sb.toString());
															countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
															
															if (countResult == 1) {
															
																sb.setLength(0);
																sb.append("UPDATE "+this.schema+".ACC6_TRANSACTION ");
																sb.append("SET CHEQUE_RECEIVING_DATE = "+chequeReceivingDate+",CHQ_STMT_KEY = '"+chqStmtKey+"', ");
																sb.append("REASON_CODE = '00' ");
																sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' ");
																System.out.println("sql line 194 >> " + sb.toString());
																dao.execDataSql(sb.toString());
																
																sb.setLength(0);
																sb.append("UPDATE "+this.schema+".ACC6_CHEQUE ");
																sb.append("SET CHEQUE_RECEIVING_DATE = "+chequeReceivingDate+" ");
																sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND SUM_CHEQUE_AMOUNT = "+chequeAmount+" ");
																System.out.println("sql line 200 >> " + sb.toString());
																dao.execDataSql(sb.toString());
																
																sb.setLength(0);
																sb.append("DELETE FROM "+this.schema+".ACC6_TEMP_CHEQUE_STMT ");
																sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND CHEQUE_AMOUNT = "+chequeAmount+" ");
																sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+" AND CHQ_STMT_KEY = '"+chqStmtKey+"' ");
																System.out.println("sql line 206" + sb.toString());
																dao.execDataSql(sb.toString());
															
															}
														}
														
														countResult = 0;
														
														sb.setLength(0);
														sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO FROM "+this.schema+".ACC6_TRANSACTION ");
														sb.append("WHERE PROVINCE_ID = '"+provinceId+"' AND CHEQUE_NO = '"+chequeNo+"' AND EXPENSE_AMOUNT = "+chequeAmount+" ");
														sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+"");
														mapResult = dao.findDataSql(sb.toString());
														countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
														
														if (countResult <= 0) {
														
															sb.setLength(0);
															sb.append("SELECT COUNT(CHEQUE_NO) AS COUNT_CHEQUE_NO FROM "+this.schema+".ACC6_TRANSACTION ");
															sb.append("WHERE CHQ_STMT_KEY = '"+chqStmtKey+"' ");
															mapResult = dao.findDataSql(sb.toString());
															countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
															
															if (countResult <= 0) {
																
																String txtCondition = "";
															
																if (dao.chkData(chequeNo).equals("")) {
																	txtCondition = " AND CHEQUE_NO IS NULL ";
																} else {
																	txtCondition = " AND CHEQUE_NO = '"+chequeNo+"' ";
																}
																
																countResult = 0;
																sb.setLength(0);
																sb.append("SELECT COUNT(PROVINCE_ID) AS COUNT_CHEQUE_NO FROM "+this.schema+".ACC6_TEMP_CHEQUE_STMT ");
																sb.append("WHERE PROVINCE_ID = '"+provinceId+"' "+txtCondition+" AND CHEQUE_AMOUNT = "+chequeAmount+" ");
																sb.append("AND TRUNC(CHEQUE_RECEIVING_DATE) = "+chequeReceivingDate+" AND CHQ_STMT_KEY = '"+chqStmtKey+"' ");
																mapResult = dao.findDataSql(sb.toString());
																countResult = Integer.parseInt(dao.chkData(mapResult.get("COUNT_CHEQUE_NO")));
																
																if (countResult <= 0) {
																	sb.setLength(0);
																	sb.append("INSERT INTO " + this.schema + ".ACC6_TEMP_CHEQUE_STMT (PROVINCE_ID,CHEQUE_NO,CHEQUE_AMOUNT,CHEQUE_RECEIVING_DATE,CREATED_DATE,CHQ_STMT_KEY) ");
																	sb.append("VALUES ('"+provinceId+"','"+chequeNo+"',"+chequeAmount+","+chequeReceivingDate+",(SYSDATE),'"+chqStmtKey+"') ");
																	dao.execDataSql(sb.toString());
																}
																
															}
														}
													}
												}
											}
											
											read = br.readLine();
											
										} // End While read file
										
										} catch (FileNotFoundException ex) { 
											System.out.println("Error FileNotFoundException : " + ex.toString());
							
										} catch (Exception ex) { 
											System.out.println("Error : " +ex.toString());
											ex.printStackTrace();
											
										} finally {
											
											 try{ 
												 
												// Return Buffer 
												br.close();
												
												// Return File
												is.close();
												
												// Return input stream file
												in.close(); 
												
											 } catch(Exception ex){ 
												 ex.printStackTrace();
											 }
										}
								conn.commit();
							}
						}
					} // End for read file data.
					
					String sql = "SELECT DOC_NO FROM " + this.schema + ".ACC6_EXPENSE WHERE EXPENSE_STATUS IN ('W','S') AND EXPENSE_TYPE = 'C'";
					List lsDocNo = dao.listDataSql(sql);
					
					for (int n = 0; n < lsDocNo.size(); n++) {
						
						Map map = (Map) lsDocNo.get(n);
						String docNo = dao.chkData(map.get("DOC_NO"));
						String num1 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE WHERE DOC_NO = '"  + docNo + "'").get("NUM"));
						String num2 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND B.REASON_CODE = '00' ").get("NUM"));
						String num3 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND (B.REASON_CODE <> '00' AND B.REASON_CODE IS NOT NULL) ").get("NUM"));
						
						if (!num2.equals("0")) {
							
							Acc6ExpenseBean aeb = new Acc6ExpenseBean();
							aeb.setDocNo(docNo);
							
							if (num1.equals(num2)) {
								aeb.setExpenseStatus("Y");
							} else {
								aeb.setExpenseStatus("S");
							}
							
							aeb.setUpdatedBy("BAAC");
							aeb.setUpdatedDate("(SYSDATE)");
							dao.update(aeb);
						
						} else if (!num3.equals("0") && num1.equals(num3)) {
							Acc6ExpenseBean aeb = new Acc6ExpenseBean();
							aeb.setDocNo(docNo);
							aeb.setExpenseStatus("N");
							aeb.setUpdatedBy("BAAC");
							aeb.setUpdatedDate("(SYSDATE)");
							dao.update(aeb);
						}
					}
					
					//cheque หลายกองทุน
					sql = "SELECT DOC_NO,SUM_CHEQUE_AMOUNT,PROVINCE_ID,CHEQUE_NO FROM " + this.schema + ".ACC6_CHEQUE WHERE CHEQUE_STATUS = 'W' ";
					sb = new StringBuffer();
					lsDocNo = dao.listDataSql(sql);
					
					for (int n = 0; n < lsDocNo.size(); n++) {
					
						Map map = (Map) lsDocNo.get(n);
						chequeNo = dao.chkData(map.get("CHEQUE_NO"));
						String docNo = dao.chkData(map.get("DOC_NO"));
						provinceId = dao.chkData(map.get("PROVINCE_ID"));
						String sumChequeAmount1 = dao.chkData(map.get("SUM_CHEQUE_AMOUNT"));
						String sumChequeAmount2 = dao.chkData(dao.findDataSql("SELECT SUM(CHEQUE_AMOUNT) AS SUM_CHEQUE_AMOUNT FROM " + this.schema + ".ACC6_CHEQUE_DETAIL A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND B.REASON_CODE = '00' ").get("SUM_CHEQUE_AMOUNT"));
						
						if (sumChequeAmount1.equals(sumChequeAmount2)) {
							//get CHEQUE_RECEIVING_DATE from table Acc6Transaction
							sb.setLength(0);
							sb.append("SELECT DISTINCT CHEQUE_RECEIVING_DATE ");
							sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
							sb.append("WHERE CHEQUE_NO = '" + chequeNo + "' AND PROVINCE_ID = '" + provinceId + "' ");
							sb.append("AND REASON_CODE = '00' ");
							Map mapChequeReceivingDate = dao.findDataSql(sb.toString()); //CHEQUE_RECEIVING_DATE
							if (!dao.chkData(mapChequeReceivingDate.get("CHEQUE_RECEIVING_DATE")).equals("")) {
								Acc6ChequeBean acb = new Acc6ChequeBean();
								acb.setDocNo(docNo);
								acb.setChequeStatus("Y");
								acb.setUpdatedDate("(SYSDATE)");
								acb.setUpdatedBy("BAAC");
								acb.setChequeReceivingDate("TO_DATE('"+this.dateUtil().changeDateFromDB(mapChequeReceivingDate.get("CHEQUE_RECEIVING_DATE").toString(), "")+"','DD.MM.YYYY')");
								dao.update(acb);
								dao.printMessage();
							}
						}
					}
				} // end list folder data  of file
			
			// Old
		    //client.disconnect();
		    
		    this.message(request).setMsg("I", "บันทึกข้อมูลเสร็จสิ้น");
		    
		    /*System.out.println(" ======= Start printMessage ACC60308 ======= ");
			dao.printMessage();
		    System.out.println(" ======= End printMessage ACC60308 ======= ");*/
			conn.commit();
				
			
			} catch (SocketException  ex){
				
				ex.printStackTrace();
				
			} catch(Exception ex) { 
				
				System.out.println("Error : " +ex.toString()); 	
				ex.printStackTrace();
				dao.printMessage();
				
				// this.message(request).setMsg("E", ex.toString() + ":" + dao.getMessage(), ex.getStackTrace());
				 	
			} finally { 
				
					if(client.isConnected()){ 
							
						try { 
									// Close Cache
									//FTP.closeCache();
							
									System.out.println(" FTP Client status :  "  + client.getStatus() );
									System.out.println(" FTP isConnection  1 : "  + client.isConnected());
									 
									// Logout 
									client.logout();
									System.out.println("Client logout.");
									
									//disconnection
									client.disconnect();
									 System.out.println("Client Disconnction.");
									 
									 System.out.println(" FTP isConnection  2 : "  + client.isConnected());
									 
							} catch (Exception ex ){ 
								System.out.println("Error : " + ex.toString());
								ex.printStackTrace();
								this.message(request).setMsg("E", ex.toString() + ":" + dao.getMessage(), ex.getStackTrace());
							}
					} // end if check connection  
					
			} // end finaly 
			
		} catch (Exception e) {
			
			try {
			
				if (conn != null) {
					conn.rollback();
				}
		
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
			dao.printMessage();
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			Acc6TempChequeStmtBean atc = new Acc6TempChequeStmtBean();
			PageBean pageBean = new PageBean();
			if (dao.chkData(pageBean.getOrderField()).equals("UPDATED_DATE")) {
				pageBean.setOrderField("CREATED_DATE");
			}
			this.beanUtil().copyRequest2Bean(request, atc);
			atc.setChqStmtKey("");
			if (!dao.chkData(atc.getChequeReceivingDate()).equals("")) {
				atc.setChequeReceivingDate("");
			}
			this.beanUtil().copyRequest2Bean(request, pageBean);
			atc.setChequeAmount(dao.chkData(atc.getChequeAmount()).replaceAll(",", ""));
			pageBean = dao.selectPage(atc, "" ,pageBean);
			if (pageBean == null) {
				this.message(request).setMsg("I", "ไม่พบข้อมูล");
			}
			dao.beanDebuger(atc);
			dao.beanDebuger(pageBean);
			request.setAttribute("pageBean", pageBean);
			request.setAttribute("provinceId", atc.getProvinceId());
			request.setAttribute("chequeNo", atc.getChequeNo());
			request.setAttribute("chequeAmount", atc.getChequeAmount());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String view(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc6TransactionBean atb = new Acc6TransactionBean();
			List lsTrn = new ArrayList();
			String provinceId = this.getAttr(request, "provinceId");
			String chequeNo = this.getAttr(request, "chequeNo");
			String chequeAmount = this.getAttr(request, "chequeAmount");
			String chequeReceivingDate = this.getAttr(request, "chequeReceivingDate");
			chequeReceivingDate = this.dateUtil().changeDateFromDB(chequeReceivingDate, "/");
			atb.setProvinceId(provinceId);
			if (!chequeNo.equals("")) {
				chequeNo = "%" + chequeNo + "%";
			}
			atb.setChequeNo(chequeNo);
			atb.setExpenseAmount(chequeAmount);
			atb.setTransactionType("C");
			Map<String, String> xcols = new HashMap<String, String>();
			xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
			lsTrn = dao.selectList(atb, xcols, " AND REASON_CODE IS NULL ORDER BY CHEQUE_DATE ASC ");
			//dao.printMessage();
			//lsTrn = dao.selectList(atb, xcols, " ORDER BY CHEQUE_DATE ASC ");
			
			StringBuffer sb = new StringBuffer();
			String coundition = "";
			chequeNo = this.getAttr(request, "chequeNo");
			if (!chequeNo.equals("")) {
				coundition = "AND CHEQUE_NO LIKE '%" + chequeNo + "%' ";
			}
			sb.append("SELECT PROVINCE_ID,CHEQUE_DATE,CHEQUE_NO,SUM_CHEQUE_AMOUNT ");
			sb.append("FROM " + this.schema + ".ACC6_CHEQUE ");
			sb.append("WHERE PROVINCE_ID = '" + provinceId + "' " + coundition + " AND SUM_CHEQUE_AMOUNT = " + chequeAmount + " AND ACTIVE = 'Y' ");
			sb.append("AND CHEQUE_STATUS = 'W' ");
			List lsData = dao.listDataSql(sb.toString());
			
			if (lsTrn.size() <= 0 && lsData.size() <= 0) {
				this.message(request).setMsg("I", "ไม่พบข้อมูล");
			}

			request.setAttribute("lsTrn", lsTrn);
			request.setAttribute("lsData", lsData);
			request.setAttribute("chequeReceivingDate", chequeReceivingDate);
			request.setAttribute("chqStmtKey", this.getAttr(request, "chqStmtKey"));
			request.setAttribute("job", "save");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				this.url = "/apps/acc6/ACC60308B.jsp";
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String save(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc6TransactionBean atb = new Acc6TransactionBean();
			Acc6TempChequeStmtBean acb = new Acc6TempChequeStmtBean();
			Acc6ExpenseChequeBean aecb = new Acc6ExpenseChequeBean();
			String transactionTimestamp = this.getAttr(request, "transactionTimestamp");
			String chqStmtKey = this.getAttr(request, "chqStmtKey");
			String[] arrChequeReceivingDate = request.getParameterValues("chequeReceivingDate");
			String[] arrChequeAmount = request.getParameterValues("chequeAmount");
			String[] arrChequeNo = request.getParameterValues("chequeNo");
			String[] arrProvinceId = request.getParameterValues("provinceId");
			String[] strArr = transactionTimestamp.split("\\|");
			int index = Integer.parseInt(strArr[1]);
			transactionTimestamp = strArr[0];
			if (!transactionTimestamp.equals("N")) {
				atb.setTransactionTimestamp("TO_TIMESTAMP('" + transactionTimestamp + "', 'YYYYMMDDHH24MISSFF3')");
				atb.setChqStmtKey(chqStmtKey);
				atb.setReasonCode("00");
				atb.setChequeReceivingDate("TO_DATE('" + arrChequeReceivingDate[index].replaceAll("/", "")+ "', 'DDMMYYYY')");
				dao.update(atb);
				aecb.setChequeReceivingDate("TO_DATE('" + arrChequeReceivingDate[index].replaceAll("/", "")+ "', 'DDMMYYYY')");
				dao.update(aecb, " TRANSACTION_TIMESTAMP = TO_TIMESTAMP('" + transactionTimestamp + "', 'YYYYMMDDHH24MISSFF3')");
				dao.delete(acb, " PROVINCE_ID = '"+arrProvinceId[index]+"' AND CHEQUE_AMOUNT = '"+arrChequeAmount[index]+"' AND CHQ_STMT_KEY = '" + chqStmtKey + "' AND CHEQUE_RECEIVING_DATE = TO_DATE('" + arrChequeReceivingDate[index].replaceAll("/", "")+ "', 'DDMMYYYY')");
			} else {
				StringBuffer sb = new StringBuffer();
				sb.append("UPDATE " + this.schema + ".ACC6_TRANSACTION ");
				sb.append("SET CHEQUE_RECEIVING_DATE = TO_DATE('" + arrChequeReceivingDate[index].replaceAll("/", "")+ "', 'DDMMYYYY'), ");
				sb.append("REASON_CODE = '00',CHQ_STMT_KEY = '" + chqStmtKey + "' ");
				sb.append("WHERE PROVINCE_ID = '"+arrProvinceId[index]+"' AND CHEQUE_NO = '" + arrChequeNo[index] + "' AND TRANSACTION_TYPE = 'C' ");
				dao.execDataSql(sb.toString());
				dao.delete(acb, " PROVINCE_ID = '"+arrProvinceId[index]+"' AND CHEQUE_AMOUNT = '"+arrChequeAmount[index]+"' AND CHQ_STMT_KEY = '" + chqStmtKey + "' AND CHEQUE_RECEIVING_DATE = TO_DATE('" + arrChequeReceivingDate[index].replaceAll("/", "")+ "', 'DDMMYYYY')");
			}
			
			String sql = "SELECT DOC_NO FROM " + this.schema + ".ACC6_EXPENSE WHERE EXPENSE_STATUS IN ('W','S') AND EXPENSE_TYPE = 'C'";
			List lsDocNo = dao.listDataSql(sql);
			for (int n = 0; n < lsDocNo.size(); n++) {
				Map map = (Map) lsDocNo.get(n);
				String docNo = dao.chkData(map.get("DOC_NO"));
				String num1 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE WHERE DOC_NO = '"  + docNo + "'").get("NUM"));
				String num2 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND B.REASON_CODE = '00' ").get("NUM"));
				String num3 = dao.chkData(dao.findDataSql("SELECT COUNT(*) AS NUM FROM " + this.schema + ".ACC6_EXPENSE_CHEQUE A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND (B.REASON_CODE <> '00' AND B.REASON_CODE IS NOT NULL) ").get("NUM"));
				if (!num2.equals("0")) {
					Acc6ExpenseBean aeb = new Acc6ExpenseBean();
					aeb.setDocNo(docNo);
					if (num1.equals(num2)) {
						aeb.setExpenseStatus("Y");
					} else {
						aeb.setExpenseStatus("S");
					}
					aeb.setUpdatedBy("BAAC");
					aeb.setUpdatedDate("(SYSDATE)");
					dao.update(aeb);
				} else if (!num3.equals("0") && num1.equals(num3)) {
					Acc6ExpenseBean aeb = new Acc6ExpenseBean();
					aeb.setDocNo(docNo);
					aeb.setExpenseStatus("N");
					aeb.setUpdatedBy("BAAC");
					aeb.setUpdatedDate("(SYSDATE)");
					dao.update(aeb);
				}
			}
			
			sql = "SELECT DOC_NO,SUM_CHEQUE_AMOUNT,PROVINCE_ID,CHEQUE_NO FROM " + this.schema + ".ACC6_CHEQUE WHERE CHEQUE_STATUS = 'W' ";
			StringBuffer sb = new StringBuffer();
			lsDocNo = dao.listDataSql(sql);
			DateUtil du = new DateUtil();
			for (int n = 0; n < lsDocNo.size(); n++) {
				Map map = (Map) lsDocNo.get(n);
				String chequeNo = dao.chkData(map.get("CHEQUE_NO"));
				String docNo = dao.chkData(map.get("DOC_NO"));
				String provinceId = dao.chkData(map.get("PROVINCE_ID"));
				String sumChequeAmount1 = dao.chkData(map.get("SUM_CHEQUE_AMOUNT"));
				String sumChequeAmount2 = dao.chkData(dao.findDataSql("SELECT SUM(CHEQUE_AMOUNT) AS SUM_CHEQUE_AMOUNT FROM " + this.schema + ".ACC6_CHEQUE_DETAIL A, " + this.schema + ".ACC6_TRANSACTION B WHERE A.DOC_NO = '"  + docNo + "' AND A.TRANSACTION_TIMESTAMP IS NOT NULL AND A.TRANSACTION_TIMESTAMP = B.TRANSACTION_TIMESTAMP AND B.REASON_CODE = '00' ").get("SUM_CHEQUE_AMOUNT"));
				if (sumChequeAmount1.equals(sumChequeAmount2)) {
					//get CHEQUE_RECEIVING_DATE from table Acc6Transaction
					sb.setLength(0);
					sb.append("SELECT DISTINCT CHEQUE_RECEIVING_DATE ");
					sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
					sb.append("WHERE CHEQUE_NO = '" + chequeNo + "' AND PROVINCE_ID = '" + provinceId + "' ");
					sb.append("AND REASON_CODE = '00' ");
					Map mapChequeReceivingDate = dao.findDataSql(sb.toString()); //CHEQUE_RECEIVING_DATE
					if (!dao.chkData(mapChequeReceivingDate.get("CHEQUE_RECEIVING_DATE")).equals("")) {
						Acc6ChequeBean acc6ChequeBean = new Acc6ChequeBean();
						acc6ChequeBean.setDocNo(docNo);
						acc6ChequeBean.setChequeStatus("Y");
						acc6ChequeBean.setUpdatedDate("(SYSDATE)");
						acc6ChequeBean.setUpdatedBy("BAAC");
						acc6ChequeBean.setChequeReceivingDate("TO_DATE('"+du.changeDateFromDB(mapChequeReceivingDate.get("CHEQUE_RECEIVING_DATE").toString(), "")+"','DD.MM.YYYY')");
						dao.update(acc6ChequeBean);
					}
				}
			}
			
			this.message(request).setMsg("I", "บันทึกข้อมูลสำเร็จ");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				request.setAttribute("chequeNo", "");
				request.setAttribute("chequeAmount", "");
				request.setAttribute("chqStmtKey", "");
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String del(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			StringBuffer sb = new StringBuffer();
			Acc6TempChequeStmtBean atc = new Acc6TempChequeStmtBean();
			this.beanUtil().copyRequest2Bean(request, atc);
			String[] chqStmtKey = request.getParameterValues("chqStmtKey");
			for (int i = 0;i < chqStmtKey.length ; i++) {
				sb.setLength(0);
				sb.append("DELETE " + this.schema + ".ACC6_TEMP_CHEQUE_STMT WHERE CHQ_STMT_KEY = '" + chqStmtKey[i] + "' ");
				//System.out.println("sql => " + sb.toString());
				dao.execDataSql(sb.toString());
			}
			atc.setChqStmtKey("");
			request.setAttribute("job", "search");
			request.setAttribute("atc", atc);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				this.search(request);
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private synchronized InputStream getFileFTPProvacc(String hostName , String  UserId ,  String password , String  nameOfFile  , String remoteFolder ) { 
		
		InputStream  in = null;
		
		try {
			in = 	FTP.getFileAsStream(nameOfFile, remoteFolder, hostName,  UserId,  password);
		}catch ( FileTransferException fileEx) {
			fileEx.printStackTrace();
			System.out.println("Error : " + fileEx.toString());
			return null;
		} catch (Exception ex){
			ex.printStackTrace();
			System.out.println("Error : " + ex.toString());
			return null;
		} finally { 
			
		}
		
		return in;
	}
			
		
}
